package com.hushan.elevator.dao;

import com.hushan.elevator.model.Department;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.QueryByExampleExecutor;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Integer>, QueryByExampleExecutor<Department> {
    // 根据上级部门ID查询部门
    List<Department> findByParentId(Integer parentId);

    // 根据部门名称查询部门
    List<Department> findByName(String name);

    // 根据创建者查询部门
    List<Department> findByCreatedBy(Integer createdBy);

    // 根据是否删除查询部门
    List<Department> findByIsDeleted(Boolean deleted);

    // 根据创建时间范围查询部门
    List<Department> findByCreatedTimeBetween(LocalDateTime start, LocalDateTime end);

    // 根据更新时间范围查询部门
    List<Department> findByUpdatedTimeBetween(LocalDateTime start, LocalDateTime end);

    @Query(value = "SELECT d.dept_id, d.parent_id, d.ancestors, d.name, d.address, d.created_by, d.created_time, d.updated_time, d.is_deleted " +
            "FROM auth_dept d " +
            "INNER JOIN auth_user_dept ud ON d.dept_id = ud.dept_id " +
            "INNER JOIN auth_user u ON ud.user_id = u.user_id " +
            "WHERE u.user_id = :userId and d.is_deleted=0", nativeQuery = true)
    Department findUserDept(Integer userId);

    @Query(value = "Select d.dept_id, d.parent_id, d.ancestors, d.name, d.address, d.created_by, d.created_time, d.updated_time, d.is_deleted from auth_dept d where find_in_set(:deptId,ancestors) and is_deleted=0", nativeQuery = true)
    List<Department> findChildByDeptId(Integer deptId);

    @Query(value = "Select COUNT(d.dept_id) from auth_dept d where find_in_set(:deptId,ancestors)", nativeQuery = true)
    int findChildCountByDeptId(Integer deptId);
}
